//
// Created by zlf on 20-1-7.
//
#include <iostream>
#include <QCoreApplication>
#include <QDebug>


#include <odb/database.hxx>
#include <odb/transaction.hxx>
#include <string>
#include <memory>   // std::auto_ptr
#include <cstdlib>  // std::exit
#include <iostream>

#include <odb/database.hxx>


#include <odb/connection.hxx>
#include <odb/transaction.hxx>
#include <odb/schema-catalog.hxx>
#include <odb/sqlite/database.hxx>

#include "notetree.h"
#include "notetree-odb.hxx"

using odb::database;
using odb::transaction;
using namespace std;

static string sqlite_file_path = "/media/zlf/_dde_data/note_test1.db";

void testInsert() {
	auto_ptr<odb::database> db(
			/*
			 * 如果指定了——create标志，那么如果数据库文件不存在，就创建它(SQLITE_OPEN_CREATE标志)。
				F如果指定了——只读标志，那么数据库将以只读模式打开(SQLITE_OPEN_READONLY标志，而不是SQLITE_OPEN_READWRITE标志)。
			 * */
			new odb::sqlite::database(sqlite_file_path, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_AUTOPROXY));

	// Create the database schema. Due to bugs in SQLite foreign key
	// support for DDL statements, we need to temporarily disable
	// foreign keys.
	//
	//创建表
//	{
//		odb::connection_ptr c(db->connection ());
//		c->execute ("PRAGMA foreign_keys=OFF");
//		odb::transaction t (c->begin ());
//		odb::schema_catalog::create_schema (*db);
//		t.commit ();
//		c->execute ("PRAGMA foreign_keys=ON");
//	}
	try {

		NoteTree noteTree1 = NoteTree();
		transaction t(db->begin());
		db->persist(noteTree1);
		t.commit();
		qDebug() << "insert-1";
	} catch (const odb::exception& e) {
		qDebug() << e.what() << endl;
		qDebug() << "insert-1-error";
	}

	try {

		NoteTree noteTree1 = NoteTree("zlssf");
		transaction t(db->begin());
		db->persist(noteTree1);

		t.commit();
		qDebug() << "insert id:" << noteTree1.id();
		qDebug() << "insert-2";
	} catch (const odb::exception& e) {
		qDebug() << e.what() << endl;
		qDebug() << "insert-2-error";
	}

	try {

		transaction t(db->begin());
		long long id = db->execute("SELECT MAX(id) FROM note_tree");
		qDebug() << "ID:" << id;

		NoteTree noteTree1 = NoteTree(id + 1, 0, "qwec");
		db->persist(noteTree1);
//		qDebug() << "insert id:" << note.data()->id();
		t.commit();
		qDebug() << "insert-3";
	} catch (const odb::exception& e) {
		qDebug() << e.what() << endl;
		qDebug() << "insert-3-error";
	}
}


void testUpdate() {
	auto_ptr<odb::database> db(
			new odb::sqlite::database(sqlite_file_path, SQLITE_OPEN_READWRITE));
	try {

		transaction t(db->begin());
		typedef odb::query<NoteTree> query;
		QSharedPointer<NoteTree> note(db->query_one<NoteTree>(query::name == "qwec"));
		if(note.data() != nullptr) {
			qDebug() << note.data()->name();
//		note.data()->setName("dd");//修改数据
			note.data()->setName("zlf");
			db->update(note);

			t.commit();

			qDebug() << note.operator*().name();
		} else {
			qDebug() << "null-----------";
			t.rollback();
		}

	} catch (const odb::exception& e) {
		qDebug() << e.what() << endl;
	}


}

void testDelete() {
	auto_ptr<odb::database> db(
			new odb::sqlite::database(sqlite_file_path, SQLITE_OPEN_READWRITE));
//	try {
//
//		transaction t(db->begin());
//		typedef odb::query<NoteTree> query;
//		QSharedPointer<NoteTree> note(db->query_one<NoteTree>(query::id == 1));
//
//		qDebug() << note.data()->name();
//		note.data()->setName("dd");//修改数据
//		db->erase(note);
//
//		t.commit();
//
//		qDebug() << note.operator*().name();
//	} catch (const odb::exception& e) {
//		qDebug() << e.what() << endl;
//	}
	try {
		transaction t(db->begin());
		typedef odb::query<NoteTree> query;
		//删除ID 等于2的数据
		db->erase_query<NoteTree>(query::id == 2);

		t.commit();

	} catch (const odb::exception& e) {
		qDebug() << e.what() << endl;
	}


}

void testQuery() {
	qDebug() << "----------------------";
	auto_ptr<odb::database> db(
			new odb::sqlite::database(sqlite_file_path, SQLITE_OPEN_READWRITE));
	try {

		transaction t(db->begin());
		typedef odb::query<NoteTree> query;
		typedef odb::result<NoteTree> result;

		result r(db->query<NoteTree>(query::true_expr));

		for (result::iterator i(r.begin()); i != r.end(); ++i)
			qDebug()  << i.id() ;
		t.commit();

	} catch (const odb::exception& e) {
		qDebug() << e.what() << endl;
	}

	qDebug() << "-----------ORDER 1-----------";

	try {

		transaction t(db->begin());
		typedef odb::query<NoteTree> query;
		typedef odb::result<NoteTree> result;
		//没有条件，将所有数据排序
		result r(db->query<NoteTree>(query::type == 1 && query::true_expr + " ORDER BY" + query::id + "desc", true));

		for (result::iterator i(r.begin()); i != r.end(); ++i)
			qDebug()  << i.id();
		t.commit();

	} catch (const odb::exception& e) {
		qDebug() << e.what() << endl;
	}

}

int main() {
//	testInsert();
//	testUpdate();
//	testDelete();
	testQuery();

	qDebug() << "end";
	return 0;
}